package commmon.db;


import com.alibaba.fastjson.JSON;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class DbConnection {
    private static final  int pageNumber=10;

    public static void toTransactionConn() throws SQLException{
        Connection con=JdbcConnectionPool.getConnection();
        con.setAutoCommit(false);
    }
    public static  int toTransactionExecuteMethod(Connection conn,String sql,Object objParam []) throws SQLException{
        //DbConnection.getConnection();
        PreparedStatement smt = null;
        int rtnFlag=0;
        if(conn!=null){
            try {
                smt=JdbcConnectionPool.getConnection().prepareStatement(sql);
                if(objParam!=null){
                    for(int i=0;i<objParam.length;i++){
                        smt.setObject(i+1, objParam[i]);
                    }
                }
                rtnFlag=smt.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                reciveResource(null, smt, conn);
            }
        }
        return rtnFlag;
    }

    public static void toTransactionCommit(Connection conn) throws SQLException{
        conn.commit();
    }

    public static  Object toExecuteMethod(Connection conn,String sql,Object objParam [],String flag){
        PreparedStatement smt=null;
        ResultSet set = null;
        int rtnFlag=0;
        if(conn!=null){
            try {
                smt=conn.prepareStatement(sql);
                if(objParam!=null){
                    for(int i=0;i<objParam.length;i++){
                        if(objParam[i]!=null){
                            smt.setObject(i+1, objParam[i]);
                        }
                    }
                }
                if(flag.equals("Q")){
                    set = smt.executeQuery();
                }else{
                    rtnFlag=smt.executeUpdate();
                }

            } catch (Exception e) {
                System.out.println("PreparedStatement执行"+sql+"Q");
                e.printStackTrace();
            }
        }
        return set==null?rtnFlag:set;
    }

    public static void reciveResource(ResultSet set,PreparedStatement smt,Connection conn){
        if(set!=null){
            try {
                set.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(smt!=null){
            try {
                smt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public static   String  showPageInfoByCurrentPage(int currentPage,String toAddress,String totalSql,Object objPram [],Map<String,String> map) throws SQLException{
        int countNumber=0;
        ResultSet set = null;
        toAddress=toAddress+"?1=1";
        Connection conn=JdbcConnectionPool.getConnection();
        try {
            set=(ResultSet) DbConnection.toExecuteMethod(conn,totalSql, objPram, "Q");
            while(set.next()){
                countNumber=set.getInt(1);
            }
            if(countNumber!=0){
                int totalPage=countNumber%pageNumber==0?pageNumber:(countNumber/pageNumber)+1;
                Set<String> set1=map.keySet();
                for(String key:set1) {
                    toAddress=toAddress+"&"+key+"="+map.get(key);
                }
                StringBuffer stringBuffer=new StringBuffer("<form action='"+toAddress+"' method='post'>");
                if(currentPage>1){
                    stringBuffer.append("<a href='"+toAddress+"&currentPage=1' style='margin-right: 20px'>首页</a><a href='"+toAddress+"&currentPage="+(currentPage-1)+"' style='margin-right: 20px'>上一页</a>");
                }
                if(currentPage<totalPage){
                    stringBuffer.append("<a href='"+toAddress+"&currentPage="+(currentPage+1)+"' style='margin-right: 20px'>下一页</a><a href='"+toAddress+"&currentPage="+totalPage+"' style='margin-right: 20px'>尾页</a>");
                }
                stringBuffer.append("<span style='margin-right: 20px'>一共'"+countNumber+"'条数据</span><span style='margin-right: 20px'>当前第'"+currentPage+"'页</span><span style='margin-right: 20px'>总共'"+totalPage+"'页 </span> <input type='text' name='currentPage' size='8'/><input type='submit' value='go'/></form>");
                return stringBuffer.toString();
            }else{
                return "";
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return "";
        }finally{
            DbConnection.reciveResource(set, null,conn);
        }
    }
    /*
     * flag参数注解 1表示需要添加修改和删除
     * 0表示不需要添加
     */
    public static String showTableInfoBySql(String sql,Object objPram [],int flag,String toAddress) throws SQLException{
        StringBuffer  tableInfoString=new StringBuffer();
        PreparedStatement smt=null;
        ResultSet set=null;
        Connection conn=null;
        try{
            conn=JdbcConnectionPool.getConnection();
            smt=conn.prepareStatement(sql);
            if(objPram!=null){
                for(int i=0;i<objPram.length;i++){
                    if(objPram[i]!=null){
                        smt.setObject(i+1, objPram[i]);
                    }
                }
            }
            set=smt.executeQuery();
            ResultSetMetaData metaData = set.getMetaData();
            int column=metaData.getColumnCount();
            tableInfoString.append("<tr>");
            for(int i=1;i<=column;i++){
                tableInfoString.append("<td>"+metaData.getColumnName(i)+"</td>");
            }
            tableInfoString.append("<td>operation</td>");
            tableInfoString.append("</tr>");

            while(set.next()){
                tableInfoString.append("<tr>");
                for(int i=1;i<=column;i++){
                    tableInfoString.append("<td>"+set.getString(i)+"</td>");
                }
                if(flag==1){
                    tableInfoString.append("<td><a href='"+toAddress+"?type=p&id="+set.getString("id")+"'>修改</a> <a href='"+toAddress+"?type=d&id="+set.getString("id")+"'>删除</a></td>");
                }
            }
            tableInfoString.append("</tr>");
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            DbConnection.reciveResource(set, smt, conn);
        }
        return tableInfoString.toString();

    }



    public  static List getListFromResultSet(ResultSet res,Class t){
        List setList=new ArrayList();
        if(res!=null)
        {
            try {
                ResultSetMetaData metaData = res.getMetaData();
                int count=metaData.getColumnCount();
                int index=0;
                while (res.next()) {
                    Map<String, Object> map=new HashMap();
                    for(int i=1;i<=count;i++){
                        //Object typeObject=t.getField(metaData.getColumnName(i)).getType();
                        map.put(metaData.getColumnName(i),res.getObject(i)==null?"":res.getObject(i));
                    }
                    System.out.println(map);
                    Object object= JSON.parseObject(JSON.toJSONString(map),t);
                    setList.add(object);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SecurityException e) {
                e.printStackTrace();
            }finally {
                reciveResource(res, null, null);
            }
        }

        return setList;
    }

    public  static Object getObjectFromResultSet(ResultSet res,Class t){
        Object object = null;
        if(res!=null)
        {
            try {
                ResultSetMetaData metaData = res.getMetaData();
                int count=metaData.getColumnCount();
                int index=0;
                while (res.next()) {
                    Map<String, Object> map=new HashMap();
                    for(int i=1;i<=count;i++){
                        //Object typeObject=t.getField(metaData.getColumnName(i)).getType();
                        map.put(metaData.getColumnName(i),res.getObject(i)==null?"":res.getObject(i));
                    }
                    System.out.println(map);
                    object= JSON.parseObject(JSON.toJSONString(map),t);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SecurityException e) {
                e.printStackTrace();
            }finally {
                reciveResource(res, null, null);
            }
        }

        return object;
    }

    public static Object getObjectFromResultSetList(ResultSet res,Class t) {
        Object object = null;
        //创建集合对象，用来接收数据并进行返回
        List<Object> staffdateList = new ArrayList<>();
        if(res!=null)
        {
            try {
                ResultSetMetaData metaData = res.getMetaData();
                int count=metaData.getColumnCount();
                int index=0;

                while (res.next()) {
                    Map<String, Object> map=new HashMap();
                    for(int i=1;i<=count;i++){
                        //Object typeObject=t.getField(metaData.getColumnName(i)).getType();
                        map.put(metaData.getColumnName(i),res.getObject(i)==null?"":res.getObject(i));
                    }
                    System.out.println(map);
                    object= JSON.parseObject(JSON.toJSONString(map),t);
                    staffdateList.add(object);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SecurityException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                reciveResource(res, null, null);
            }
        }

        return staffdateList;
    }
}
